Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

I'm glad you're back! Today we're going to focus on what's involved in ensuring your customers can check out from your online store. Now that your customer has a shopping cart full of merchandise, you want their checkout experience to be as painless as possible.

First, we'll look at the different processes involved in checking out. You'll learn to register new customers and allow returning customers to breeze through the checkout process. Next, you'll find out what's involved in safeguarding your customer's information as they check out. There's a lot of personal information required, and you wouldn't want that to fall into the wrong hands.

Finally, you'll discover what's involved with verifying and processing a new customer registration form.

That should keep us busy for the day. Let's go on to Chapter 2 and start talking about the checkout lane.

Checking Out

After your customers have had the opportunity to select products from the online store, they'll want to check out and purchase the items. The checkout process is a three-part adventure:

1. The customer logs into the storefront with an existing account or registers for a new account.
2. The customer either confirms or modifies the order.
3. The storefront application program creates a record for the new order with the proper information for processing.

The Food Store application handles these three processes using several code files, as shown.

The checkout process program flow

The first step in the process is to determine if the customer is a returning customer. The Food Store keeps information about current customers in the customers table. This helps shorten the checkout process for returning customers, as they don't have to enter any shipping information because you already have it.

When the customer is ready to check out, the navigation bar provides a link to the index.php file using the checkout.inc.php include file. This is the starting point of the checkout process. Let's build that code file.

1. Create the file checkout.inc.php in the store folder in your application area.
2. Open the file with a text editor, and add the following code:

Print code


<h2><u>Check Out Procedure</u></h2><br><br>
<p>If you are a returning customer, please enter your e-mail address and password<p>
<form action="verifycust.php" method="post">
<table width="50%" cellpadding="1" border="1">
<tr>
   <td>e-mail address</td>
   <td><input type="text" name="email"></td>
</tr>
<tr>
   <td>password</td>
   <td><input type="password" name="password1"></td>
</tr>
</table>
<input type="submit" name="button" value="Login">
</form>
<br><br><br>
<form action="index.php" method="get">
<input type="submit" name="button" value="Click here if you're a new customer">
<input type="hidden" name="content" value="newcust">
</form>

3. Save the file and exit the editor.

This code creates two separate forms on the Web page. The first form asks for the e-mail address and password if the person is a returning customer. If you've never used them before, the password type of input form works just like the text form but hides the text that's typed in it. This helps keep some sense of security as your customer enters a password.

The other form on the Web page is just a simple submit button for the person to click if they're a new customer. This sends the customer to another page to enter his or her personal information (which we'll walk through in a little bit). Each form has its own action attribute to pass control off to a different code file, depending on which form button the customer selects.

The customer checkout page

If the customer is a returning customer, the form action sends the data to the verify.php file. (Note that this isn't part of the index.php include files; I'll explain that in a little bit). If the customer is a new customer, the form action sends the data to the index.php file, using the newcust.inc.php include file.

First, let's take a look at how to handle returning customers.

Processing Returning Customers

If the customer is a returning customer, he or she will already have a record on file in the customers table. The login form asks for his or her e-mail address and password. You'll need to verify the e-mail address and password provided. We'll use the verifycust.php file to do that.

By making this a separate file apart from the index.php include files, we can use the header() function to automatically redirect the customer to the next step in the process without any manual intervention on the customer's part. After the program verifies the customer login information, it passes directly to the next step in the checkout process.

Let's create the verifycust.php file and then take a look at what it does.

1. Create the file verifycust.php in the store folder of the application area.
2. Open the file in a text editor and enter the following code:

Print code


<?php
   session_start();
   include("mylibrary/login.php");
   login();
   $email = $_POST['email'];
   $password = $_POST['password1'];
   $query = "SELECT * from customers where email = '$email' and password = PASSWORD('$password')";
   $result = mysql_query($query);
   $row = mysql_num_rows($result);
   if ($row)
   {
      $row = mysql_fetch_array($result, MYSQL_ASSOC);
      $custid = $row['custid'];
      $_SESSION['cust'] = $custid;
      header("Location: index.php?content=confirmorder");
   } else
   {
      echo "<h2>Sorry, Unable to verify customer</h2>\n";
      echo "<a href=\"index.php?content=checkout\">Go back to check out</a>\n";
   }
?>

3. Save the file and exit the editor.

Because this isn't part of the index.php code, you'll have to use the session_start() and login() functions here to access the session cookies and log into the MySQL server. After retrieving the form data, it prepares a SELECT query to check the e-mail address and password in the customers table. The password is stored in the customer table using the PASSWORD() function, which produces a 41-byte encrypted password from a text string. You must also use the PASSWORD() function in the SQL query to generate the encrypted password based on the text value entered into the form.

If they match, it creates a new session cookie called cust, places the assigned customerid value from the table in it, then redirects the customer's browser to the next Web page in the checkout process. This is the confirmorder page.

If the user account and password values don't match what's in the database, the customer is asked to go back to the main checkout page and try again. This just provides a quick link back to the checkout Web page.

Let's put the new customer registration programs off to the side for a little while and follow the flow to the next step in the process, the confirmorder section. This is where the customer can confirm or reject the order.

Confirming an Order

After the returning customer has logged in (or a new customer has registered), you want to offer them the ability for one last chance to change their order. The confirmorder.inc.php include file provides them that last chance.

Let's create this file for the application.

1. Create the file confirmorder.inc.php in the store folder in the application area.
2. Open the file in a text editor and add the following code:

Print code


<?php
   echo "<h2><u>Confirming Order</u></h2><br>\n";
   $total = 0;
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Product</td><td>Price</td><td>Quantity</td><td>Total</td>\n";
   foreach($_SESSION['cart'] as $prodid => $quantity)
   {
      $query = "SELECT description, price FROM products WHERE prodid = $prodid";
      $result = mysql_query($query);
      $row = mysql_fetch_array($result, MYSQL_ASSOC);
      $description = $row['description'];
      $price = $row['price'];
      $subtotal = $price * $quantity;
      $total += $subtotal;
       printf("<tr><td>%s</td><td>%s</td><td>%d</td><td>$%.2f</td></tr>\n",
              $description, $price, $quantity, $subtotal);
   }
   printf("<tr><td colspan=\"3\"><b>Total</b></td><td>$%.2f</td></tr>\n", $total);
   echo "</table>\n";
   echo "<form action=\"index.php\" method=\"post\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"finishorder\">\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Confirm order\">\n";
   echo "</form>\n";
   echo "<form action=\"index.php\" method=\"post\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"reviewcart\">\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Change Order\">\n";
   echo "</form\">\n";
?>

3. Save the file and exit the editor.

The confirmorder.inc.php file extracts the information from the shopping cart and displays it in a table in the main section of the storefront Web page. This page creates two buttons after the shopping cart information. The customer has the option to return to the review cart page to modify the cart contents or proceed with checking out.

The confirm order page

That's enough coding for this chapter. In Chapter 3, we'll pick up the checkout story by showing how to register a new customer.

Registering a New Customer

When you have a brand-new customer, you'll need to get their basic information, such as first and last names and shipping address. Besides this information, you'll also want to create a login account for them so they can be a returning customer the next time they shop on your site. Often sites use an e-mail address as a login ID. This not only makes it easier for the customer to remember, but it gives you an easy way of acquiring customer contact information for later advertisements!

When the customer clicks the button to register in the checkout.inc.php Web page, it sends him or her to the main index.php page, setting the content variable to newcust. This link uses the newcust.inc.php include file in the main section of the Web page. Let's build that file and then take a look at what it does.

1. Create the file newcust.inc.php in the store folder in your application area.
2. Open the file with a text editor and add the following code:

<h2>Welcome, new customer!</h2><br><br>
<p>Please fill out this form so we can send you your products<p>
<form action="addcust.php" method="post">
<table width="50%" cellpadding="1" border="1">
<tr>
   <td>First name</td>
   <td><input type="text" size="40" name="firstname"></td>
</tr>
<tr>
   <td>Last name</td>
   <td><input type="text" size="40" name="lastname"></td>
</tr>
<tr>
   <td>Address</td>
   <td><input type="text" size="60" name="address"></td>
</tr>
<tr>
   <td>City</td>
   <td><input type="text" size="30" name="city"></td>
</tr>
<tr>
   <td>State</td>
   <td><input type="text" size="2" name="state"></td>
</tr>
<tr>
   <td>Zip</td>
   <td><input type="text" size="5" name="zip"></td>
</tr>
<tr>
   <td>Phone</td>
   <td><input type="text" size="15" name="phone"></td>
</tr>
<tr>
   <td>e-mail address</td>
   <td><input type="text" size="60" name="email"></td>
</tr>
<tr>
   <td>password</td>
   <td><input type="password" size="15" name="password1"></td>
</tr>
<tr>
   <td>Confirm password</td>
   <td><input type="password" size="15" name="password2"></td>
</tr>
</table>
<input type="submit" name="button" value="Submit form">
</form>

3. Save the file and exit the text editor.

That's a lot of code, but it's all pretty basic. It just creates a single HTML form, with textboxes for all of the data you need to collect from the customer. Note the use of the password input types again to help hide the passwords from view.

The new customer registration page

After the customer fills out the necessary information and clicks the submit button, the information is sent to the Web page defined in the action attribute of the form. Here's another place that may need some security in your application.

HTML Form Security

Your new customer registration form requests personal information from the customer. These days it's not always a good idea to send that information across the Internet as plain text. Instead, you should use encryption to hide the data as the client sends it.

HTTP is a text-based protocol. The transfer of all data that the server sends to the client, as well as data the client returns to the server is in plain text format. This provides a window of opportunity for someone to monitor and steal your customer's personal information.

However, there's a way to encrypt the data before sending it across an HTTP connection. After receiving the encrypted data on the other end, it must be decrypted before the client or the Web server can process it.

A secure HTTP connection (called HTTPS) provides a method for using an encryption key on the server for encrypting data sent by the client to the server. This is called a public encryption key. The server shares its encryption key with the client (the reason it's called public), which uses the key to encrypt the data. When the server receives the encrypted data, it uses a private encryption key to decrypt the data. Only the private encryption key can decrypt data encrypted using the public encryption key.

Using public and private encryption keys

There are two types of private and public encryption keys:

• A self-signed encryption key
• An authority-signed encryption key

Just because a site sends a public encryption key, it doesn't mean you can trust it. There's a method of authenticating encryption keys in which a trusted security company certifies that an encryption key is valid. This is called signing.

When a trusted security company signs an encryption key, it validates that the encryption key belongs to the issuing company. When your browser receives a signed public encryption key, it can verify the signature on the key and, thus, verify the authenticity of the encryption key.

Most ISPs provide encrypted hosting, but usually for an additional fee. With encrypted hosting, you receive the public and private encryption keys to encrypt and decrypt data for your Web site. Unfortunately, the WampServer doesn't support this by default, so you won't be able to test it on your development server.

Posting Data Using HTTPS

When you connect to a secure Web site, you must use a different protocol specification in the URL. For a normal HTTP Web site, you usually create a URL like:

http://localhost/store/

For the customer's browser to send data using the encryption key, they must specify the HTTPS protocol name in the URL:

https://localhost/store/

However, you don't need to use a secure Web connection for the entire shopping session. All you need to secure is the personal information that the customer sends to your host.

If your Web site supports encryption, you can easily do this by modifying the action attribute of the HTML form:

<form action="https://servername/addcust.php" method="post">

By specifying the HTTPS form of the Web page in the form action attribute, you force the client's browser to send the data using the public encryption key your server provides, even if they used the HTTP format to access the original Web page for the form. Note that when you use this format you need to include the full servername in your URL.

After the addcust.php file does its thing, it can continue the Web session using the normal HTTP method. Let's continue on to Chapter 4 and look at the addcust.php program.

Adding the Customer Data

Once you receive the form data (either as encrypted data or plain text), you need to create a new record in the customers table. The addcust.php file does this for us. Let's build that file now.

1. Create the file addcust.php in the store folder of your application area.
2. Open the file with a text editor and add the following code:

Print code


<?php
   session_start();
   include("mylibrary/login.php");
   login();
   $firstname = $_POST['firstname'];
   $lastname = $_POST['lastname'];
   $address = $_POST['address'];
   $city = $_POST['city'];
   $state = $_POST['state'];
   $zip = $_POST['zip'];
   $phone = $_POST['phone'];
   $email = $_POST['email'];
   $password1 = $_POST['password1'];
   $password2 = $_POST['password2'];
   if (get_magic_quotes_gpc())
   {
      $firstname = stripslashes($firstname);
      $lastname = stripslashes($lastname);
      $address = stripslashes($address);
      $city = stripslashes($city);
      $state = stripslashes($state);
      $zip = stripslashes($zip);
      $phone = stripslashes($phone);
      $email = stripslashes($email);
      $password1 = stripslashes($password1);
      $password2 = stripslashes($password2);
   }
   $firstname = mysql_real_escape_string($firstname);
   $lastname = mysql_real_escape_string($lastname);
   $address = mysql_real_escape_string($address);
   $city = mysql_real_escape_string($city);
   $state = mysql_real_escape_string($state);
   $zip = mysql_real_escape_string($zip);
   $phone = mysql_real_escape_string($phone);
   $email = mysql_real_escape_string($email);
   $password1 = mysql_real_escape_string($password1);
   $password2 = mysql_real_escape_string($password2);

   $baduser = 0;
   if (trim($email) == '')
      $baduser = 1;
   if (trim($password1) == '')
      $baduser = 2;
   if ($password1 != $password2)
      $baduser = 3;
   $query = "SELECT * from customers where email = '$email'";
   $result = mysql_query($query);
   $rows = mysql_num_rows($result);
   if ($rows != 0)
      $baduser = 4;
   if ($baduser == 0)
   {
      $query = "INSERT INTO customers (firstname, lastname, address, city, state, " .
              "zip, phone, email, password) VALUES ('$firstname', '$lastname' , " .
              " '$address', '$city', '$state', '$zip' , '$phone', '$email', " .
              " PASSWORD('$password1'))";
      $result=mysql_query($query);

      if ($result)
      {
         $query = "SELECT LAST_INSERT_ID() from customers";
         $result = mysql_query($query);
         $row = mysql_fetch_array($result);
         $_SESSION['cust'] = $row[0];
         header("Location: index.php?content=confirmorder");
      }
      else
      {
         echo "<h2>Sorry, I could not process your form at this time</h2>\n";
      }
   } else
   {
      switch($baduser)
      {
         case(1):
            echo "<h2>Please enter an e-mail address</h2>\n";
            break;
         case(2):
            echo "<h2>Please enter a password</h2>\n";
            break;
         case(3):
            echo "<h2>Your passwords did not match!</h2>\n";
            break;
         case(4):
            echo "<h2>I'm sorry, that e-mail address already exists.</h2>\n";
       }
       echo "<a href=\"index.php?content=newcust\">Try again</a>\n";
   }
?>

3. Save the file and exit the text editor.

You should recognize this code. However, I threw in a couple of tricks that you may not be familiar with. Let's take a closer look at those.

Verifying User Data

The first thing the program does is the standard data normalization for the MySQL query. You should remember this technique from back in the back-end application. All of the data retrieved from the HTML form is checked for the proper MySQL format, and escape characters are added as necessary using the mysql_real_escape_string() function.

Following that, you'll notice that I perform a few checks on the data. I create a PHP variable called $baduser to track if any of the checks fail. A different value is stored in the $baduser variable if the check fails. At the end of the checks, all you need to do is check the final value of the $baduser variable. If it's still zero, you know all of the checks passed. If it has a non-zero value, then you know something failed.

You could check the value of the $baduser variable using multiple if-then-else statements. However, this would take several layers of "ifs" to process and would look pretty messy.

A better way to check the value of a variable is to use the switch-case statement. The switch-case statement defines a variable to use as the switch and then allows you to define actions to take based on the value contained in the switch variable. The PHP preprocessor jumps to the appropriate case statement that contains the value in the variable and then processes the statements from there.

At the end of each case section, you need to include the break statement. The break statement tells PHP to jump out of the switch section instead of continuing with the next statements.

Getting the New Customer ID

Once the new customer has registered on the system, you'll need to store the new customer ID value in the cust session cookie, just as we did for a returning customer. However, there's a small problem with that.

When you use the INSERT statement to create a new customer record, the MySQL server uses the auto_increment attribute of the custid data field to assign a new customer ID value. Unfortunately, it doesn't tell you what that value is. You need to go back to the database to retrieve it.

This can cause a dilemma. How do you know what customer ID value the server assigned to your record? One method would be to create a SELECT statement, retrieving the custid value for the e-mail address you have. The method I chose utilizes a little MySQL server trick.

The LAST_INSERT_ID() function returns the most recently generated auto_increment value on a per-connection basis. This means that even if you have hundreds of customers registering at the same time, you're guaranteed to get the last ID that was generated by the current connection. This is a great way to retrieve the auto_increment value quickly for an inserted record.

After retrieving the new customer's ID, addcust.php stores it in the cust session cookie. It redirects the customer to the next step in the process, which is to confirm the order using the confirmorder.inc.php file that you saw earlier for returning customers. This takes us full circle in the checkout process. The application validates both new and returning customers and is ready to process the order. We'll save that part for the next lesson.

Let's take a break from the customer checkout process for today. Chapter 5 will wrap things up and get us ready for the next lesson.

Summary

Today we covered a large piece of the customer checkout experience. The goal of the Food Store is to make the customer checkout process as painless as possible for the customer. When the customer clicks the Check Out link in the navigation menu, the application first takes him or her to a simple two-part form.

If the customer is a returning customer, they can easily log in using their registered e-mail address and password. The Food Store application stores customer information in a table;.So you can easily look up the customer's information and verify the login attempt. If the customer successfully logs in, the next step in the checkout process is to allow the customer to confirm the order.

If the customer is new, they can register via a standard HTML form. We discussed how to use a secure HTTP session to help protect the customer's data when submitting the form. The secure HTTP session uses a public and private encryption key to encrypt the data the customer submits in the HTML form. This requires a special TCP port on the server, along with the proper encryption keys.

After the customer submits the registration information, your application must create the new customer account. You learned how to validate the submitted form information using specific data checks and how to use the switch-case statement to produce error messages easily if any of the data fails. Finally, after inserting the new customer data into the customers table, the application code uses the LAST_INSERT_ID() MySQL function to determine the auto_increment value assigned to the new customer. That value is then stored in the session cookie for use later on in the process.

In the next lesson, we'll finish off the checkout process. This includes updating all of the appropriate database tables to ensure the shopping cart data is converted into an order, as well as looking at what the store manager must do to process orders.